package cn.edu.zafu.bigdata.yuyini;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.sql.*;
import java.util.Scanner;

class Student_Database {
    private String id;
    private String name;
    private String phone;

    public Student_Database(String id, String name, String phone) {
        this.id = id;
        this.name = name;
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Student_Database{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

class DatabaseConnection {
    public static Connection getConnection() {
        String url = "jdbc:mysql://192.168.169.76:3306/learnjava";
        String user = "learn";
        String password = "learnpassword";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("数据库连接失败: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }
}

public class StudentManagementSystem_Database {
    private static Connection connect() {
        return DatabaseConnection.getConnection();
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        Connection conn = connect();
        if (conn == null) {
            System.out.println("数据库连接失败。");
            return;
        }

        try {
            while (true) {
                System.out.println("学生信息管理系统");
                System.out.println("1. 添加学生信息");
                System.out.println("2. 检索学生信息");
                System.out.println("3. 更新学生信息");
                System.out.println("4. 删除学生信息");
                System.out.println("5. 退出程序");
                System.out.print("请输入操作编号 (1-5): ");
                String input = scanner.nextLine();

                switch (input) {
                    case "1":
                        addStudentInfo(conn, scanner);
                        break;
                    case "2":
                        retrieveStudentInfo(conn, scanner);
                        break;
                    case "3":
                        updateStudentInfo(conn, scanner);
                        break;
                    case "4":
                        deleteStudentInfo(conn, scanner);
                        break;
                    case "5":
                        System.out.println("程序退出。");
                        return;
                    default:
                        System.out.println("无效的输入，请重新输入。");
                        break;
                }
            }
        } catch (SQLException e) {
            System.out.println("数据库操作失败。");
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            scanner.close();
        }
    }

    private static void addStudentInfo(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入学号: ");
        String id = scanner.nextLine();
        while (checkIfStudentExists(conn, id)) {
            System.out.println("学号已存在，请重新输入学号: ");
            id = scanner.nextLine();
        }
        System.out.print("请输入姓名: ");
        String name = scanner.nextLine();
        System.out.print("请输入电话: ");
        String phone = scanner.nextLine();

        String sql = "INSERT INTO student_202211080202 (id, name, phone) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, id);
            stmt.setString(2, name);
            stmt.setString(3, phone);
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("学生信息添加成功。");
            } else {
                System.out.println("学生信息添加失败。");
            }
        }
    }

    private static boolean checkIfStudentExists(Connection conn, String id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM student_202211080202 WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        }
    }

    private static void retrieveStudentInfo(Connection conn, Scanner scanner) throws SQLException {
        while (true) {
            System.out.print("请输入学号进行检索: ");
            String id = scanner.nextLine();
            if ("QUIT".equalsIgnoreCase(id)) {
                break;
            }

            String sql = "SELECT * FROM student_202211080202 WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, id);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    Student_Database student = new Student_Database(id, rs.getString("name"), rs.getString("phone"));
                    System.out.println(student.toString());
                } else {
                    System.out.println("未找到该学生。");
                }
            }
        }
    }

    private static void updateStudentInfo(Connection conn, Scanner scanner) throws SQLException {
        while (true) {
            System.out.print("请输入学号进行更新: ");
            String id = scanner.nextLine();
            if ("QUIT".equalsIgnoreCase(id)) {
                break;
            }

            if (!checkIfStudentExists(conn, id)) {
                System.out.println("未找到该学生。");
                continue;
            }

            System.out.print("请输入新的姓名: ");
            String name = scanner.nextLine();
            System.out.print("请输入新的电话: ");
            String phone = scanner.nextLine();

            String sql = "UPDATE student_202211080202 SET name = ?, phone = ? WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, name);
                stmt.setString(2, phone);
                stmt.setString(3, id);
                int rowsAffected = stmt.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("学生信息更新成功。");
                } else {
                    System.out.println("学生信息更新失败。");
                }
            }
        }
    }

    private static void deleteStudentInfo(Connection conn, Scanner scanner) throws SQLException {
        while (true) {
            System.out.print("请输入学号进行删除: ");
            String id = scanner.nextLine();
            if ("QUIT".equalsIgnoreCase(id)) {
                break;
            }

            if (!checkIfStudentExists(conn, id)) {
                System.out.println("未找到该学生。");
                continue;
            }

            String sql = "DELETE FROM student_202211080202 WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, id);
                int rowsAffected = stmt.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("学生信息删除成功。");
                } else {
                    System.out.println("学生信息删除失败。");
                }
            }
        }
    }
}